package jdbc;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 测试德鲁伊连接池
 */
@SuppressWarnings({"all"})
public class JDBCUtilsByDruid_USE {

    public static void main(String[] args) {
        /*
            使用Druid连接池的问题
            1.关闭connection后，resultSet结果集无法使用
            2.resultSet不利于数据管理
         */
    }

    @Test
    public void testSelect() {
        System.out.println("使用druid连接池");
        //1. 得到连接
        Connection conn = null;
        //2.组织sql
        String sql = "select * from actor where id >=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        //3.创建PreparedStatement对象
        try{
            conn = JDBCUtilsByDruid.getConnection();
            System.out.println(conn.getClass());    //运行类型 com.alibaba.druid.pool.DruidPooledConnection
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            rs = ps.executeQuery();
            //遍历结果集
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                Date borndate = rs.getDate("borndate");
                String phone = rs.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(conn, ps, rs);
        }
    }

    //普通方法来解决ResultSet =封装=> ArrayList
    @Test
    public List<Actor> testSelectToArrayList() {
        System.out.println("使用druid连接池");
        //1. 得到连接
        Connection conn = null;
        //2.组织sql
        String sql = "select * from actor where id >=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Actor> list = new ArrayList<>();
        //3.创建PreparedStatement对象
        try{
            conn = JDBCUtilsByDruid.getConnection();
            System.out.println(conn.getClass());    //运行类型 com.alibaba.druid.pool.DruidPooledConnection
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            rs = ps.executeQuery();
            //遍历结果集
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                Date borndate = rs.getDate("borndate");
                String phone = rs.getString("phone");
//                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
                //把得到的 resultSet 的记录，封装到 Actor对象，放入到 List 集合
                list.add(new Actor(id, name, sex, borndate, phone));
            }
            System.out.println("list集合数据=" + list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(conn, ps, rs);
        }
        return list;
    }
}
